package com.example.demo.dao;

import com.example.demo.model.Book;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 图书数据访问层
 */
public class BookDAO {
    private final HikariDataSource dataSource;

    public BookDAO(HikariDataSource dataSource) {
        this.dataSource = dataSource;
    }

    // 插入新书籍
    public boolean insertBook(Book book) {
        String sql = "INSERT INTO books(title, author, publisher, publish_date, " +
                   "isbn, pages, price, category, status, description, cover_url) " +
                   "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getPublisher());
            pstmt.setDate(4, Date.valueOf(book.getPublishDate()));
            pstmt.setString(5, book.getIsbn());
            pstmt.setInt(6, book.getPages());
            pstmt.setBigDecimal(7, book.getPrice());
            pstmt.setString(8, book.getCategory());
            pstmt.setString(9, book.getStatus().name());
            pstmt.setString(10, book.getDescription());
            pstmt.setString(11, book.getCoverUrl());

            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            System.err.println("Error inserting book: " + e.getMessage());
            return false;
        }
    }

    // 根据ID查询书籍
    public Book getBookById(Long id) {
        String sql = "SELECT * FROM books WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                Book book = new Book();
                book.setId(rs.getLong("id"));
                book.setTitle(rs.getString("title"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPublishDate(rs.getDate("publish_date").toLocalDate());
                book.setIsbn(rs.getString("isbn"));
                book.setPages(rs.getInt("pages"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setCategory(rs.getString("category"));
                book.setStatus(Book.Status.valueOf(rs.getString("status")));
                book.setDescription(rs.getString("description"));
                book.setCoverUrl(rs.getString("cover_url"));
                book.setCreatedAt(rs.getObject("created_at", LocalDateTime.class));
                return book;
            }

        } catch (SQLException e) {
            System.err.println("Error getting book by ID: " + e.getMessage());
        }
        
        return null;
    }

    // 更新书籍状态
    public boolean updateBookStatus(Long id, Book.Status status) {
        String sql = "UPDATE books SET status = ? WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, status.name());
            pstmt.setLong(2, id);
            
            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            System.err.println("Error updating book status: " + e.getMessage());
            return false;
        }
    }

    // 删除书籍
    public boolean deleteBook(Long id) {
        String sql = "DELETE FROM books WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);
            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            System.err.println("Error deleting book: " + e.getMessage());
            return false;
        }
    }

    // 查询所有书籍
    public List<Book> getAllBooks() {
        String sql = "SELECT * FROM books";
        List<Book> books = new ArrayList<>();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getLong("id"));
                book.setTitle(rs.getString("title"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPublishDate(rs.getDate("publish_date").toLocalDate());
                book.setIsbn(rs.getString("isbn"));
                book.setPages(rs.getInt("pages"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setCategory(rs.getString("category"));
                book.setStatus(Book.Status.valueOf(rs.getString("status")));
                book.setDescription(rs.getString("description"));
                book.setCoverUrl(rs.getString("cover_url"));
                book.setCreatedAt(rs.getObject("created_at", LocalDateTime.class));
                books.add(book);
            }

        } catch (SQLException e) {
            System.err.println("Error getting all books: " + e.getMessage());
        }
        
        return books;
    }
}